4.4 Archiving jobs

The Jobs table in the MyID database is used for managing active issuance jobs as well as retaining a history of completed jobs. Over time, the information on completed jobs can build up and ultimately reduce performance due to the size of the data. As a solution to this, you can archive the completed, failed, and canceled jobs over a certain age.

Warning: The instructions in this document allow you to archive the job information. You must check Microsoft documentation for full operating instructions for Microsoft SQL Server.

You can store the archived job information in the main MyID database, or alternatively you can configure a separate database to store this information.

4.4.1 Setting up a separate database for the jobs archive

If you want to use a separate database, you can select the Archive Database Server option when installing MyID to create an archive database, and carry out the following procedure:

  1. Create an archive database to use for your jobs archive.

    The installation procedure is the same as creating a separate audit database; see section 4.1, Using a separate audit database.

  2. On the MyID application server, update the archive.udl file to point to the correct database:

    1. Open a Windows command prompt as an Administrator.
    2. Navigate to the Windows System32 folder.

    3. Type the name of the archive.udl file, then press Enter.

      The archive.udl filename has the format:

      <databasename>archive.udl

      where <databasename> is the name of the main MyID database ; for example, MyIDarchive.udl

      This opens the Data Link Properties dialog, which allows you to change the data link file.

    4. Set the properties to point to the server and database you created to store the archived job information.

  3. In the main MyID database, edit the ArchiveDatabaseLocation function:

    1. Locate the following:

      Copy 
      ALTER FUNCTION [dbo].[ArchiveDatabaseLocation] ( )
      RETURNS sysname
      AS
      BEGIN
          DECLARE @Location sysname
          select @Location = db_name()
          RETURN @Location
      END

    2. Change the function to return the name of your archive database instead of the database; for example:

      Copy 
      ALTER FUNCTION [dbo].[ArchiveDatabaseLocation] ( )
      RETURNS sysname
      AS
      BEGIN
          RETURN 'MyIDArchive'
      END

    3. Run the query to update the function.

    4. Carry out one of the following:

      • Re-run the installation procedure to install the main MyID database again.

        Note: The installation program does not allow you to re-run the database installation without uninstalling the database component first. Instead, you can run the installer from a PC that does not have MyID installed, and select only the database option; this re-runs the scripts against the existing database.

      • If you are using Project Designer to customize your system, re-run the Project Designer scripts.

      This updates the following views in the MyID database:

      • mis_PIVArchivedRequests

      • mis_PIVAllRequests

      These views are used for the Archived Requests and All Requests reports in the MyID Operator Client, to allow the reports to include information from the archive database.

      Note: You do not have to update the function again if you upgrade MyID. You need to update the function only if you change the name of the archive database at a later date.

Note: If you add a separate jobs archive database after initially storing your archived jobs in the main MyID database, the stored procedure does not copy the archived jobs from the main database to the archive database; you must migrate this data manually.

4.4.2 Running the stored procedure

This procedure is performed on the SQL server that stores the live job information. You can either run the procedure manually or set up a timed task; see your Microsoft documentation for details of creating an SQL timed task.

The syntax of the stored procedure is:

sp_ArchiveJob '<database>', <daysOld>

where:

When this procedure runs, all completed, failed or canceled job data that is more than 90 days old is moved from the jobs table to the job archive table.

4.4.3 Testing the job archive process

You can use the following stored procedure to test the job archive process:

sp_archiveJobCopy

This stored procedure operates in the same way as sp_ArchiveJob, but does not remove job data from the Jobs table after copying it to the archive table.

4.4.4 Database views

You can use the following views to assist in reporting data from the primary and archive job tables:

Note: These views return data from the current database only. They do not access information in a separate jobs archive database.

You can also use the following views, which provide a more limited set of fields, but are designed to include data from both the main MyID database and the archive database, if configured:

4.4.5 Viewing archived jobs

The Archived Requests and All Requests reports in the MyID Operator Client allow you to view request jobs that have been archived.

If you are using a separate archive database, and you do not see archived information in these reports, make sure you have updated the ArchiveDatabaseLocation function and the mis_PIVArchivedRequests and mis_PIVAllRequests views; see section 4.4.1, Setting up a separate database for the jobs archive for details.

See the Archived Requests report and All Requests report sections in the MyID Operator Client guide for details of running the reports.